USE [ServerMonitor]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[AllServerLastWeekDBSize]'))
DROP VIEW [dbo].[AllServerLastWeekDBSize]
GO
USE [ServerMonitor]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[AllServerLastWeekDBSize]
AS
  SELECT SN.Name,SN.IP,SN.Instance,A.[DBName]
      ,CAST([DataSize] * 8.0 / 1024 AS DECIMAL(10,2)) AS DataSizeMB
      ,CAST([DataUsage]/([DataSize] * 1.0) AS DECIMAL(10,2)) AS DataUsage
      ,(SELECT MIN([DiskFreeMB])
      FROM [ServerMonitor].[dbo].[AllServerDBFile]
      WHERE [DBUsageId] = A.Id AND [FileType] = 'DATA') AS DataDiskMinFreeMB
      ,CAST([LogSize] * 8.0 / 1024 AS DECIMAL(10,2)) AS LogSizeMB
      ,CAST([LogUsage]/([LogSize] * 1.0) AS DECIMAL(10,2)) AS LogUsage   
      ,(SELECT MIN([DiskFreeMB])
      FROM [ServerMonitor].[dbo].[AllServerDBFile]
      WHERE [DBUsageId] = A.Id AND [FileType] = 'LOG') AS LogDiskMinFreeMB      
      ,A.[RecordTime]
  FROM [ServerMonitor].[dbo].[AllServerDBUsage] AS A INNER JOIN
  (SELECT MAX(RecordTime) AS RecordTime,[ServerId],[DBName]
  FROM [ServerMonitor].[dbo].[AllServerDBUsage]
  WHERE DATEDIFF(DAY,RecordTime,GETDATE()) = 7
  GROUP BY [ServerId],[DBName]) AS B
  ON A.[RecordTime] = B.RecordTime AND A.[DBName] = B.DBName AND A.ServerId = B.ServerId
  LEFT JOIN [ServerMonitor].[dbo].[Server] AS SN
  ON A.ServerId = SN.Id







GO


